﻿Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Diagnostics
Imports System.Linq
Imports IETracker.Business.Entities
Imports System.Collections.ObjectModel

Public Class IETrackerDataAccess
    Private Const ENTITY_SET_NAME_PETROL As String = "Petrols"
    Private Const ENTITY_SET_NAME_TRANSACTIONS As String = "Transactions"
    Private Const ENTITY_SET_NAME_TRANSACTIONDESCRIPTION As String = "TransactionDescriptions"
    Private Const ENTITY_SET_NAME_TRASNSACTIONTYPE As String = "TransactionTypes"
    Private Const ENTITY_SET_NAME_TRASNSACTIONTYPEDESCRIPTION As String = "TransactionTypeDescriptions"
    Private Const ENTITY_SET_NAME_VEHICLE As String = "Vehicles"

#Region "Transactions"
    Public Function ReadAllTransactions() As List(Of Transaction)
        Dim _transactions As List(Of Transaction) = Nothing
        Using ctx As New IETrackerDataContext
            _transactions = (From _trans In ctx.Transactions).ToList
        End Using
        Return _transactions
    End Function

    Public Function CreateTransaction(_transaction As Transaction) As Transaction
        Using ctx As New IETrackerDataContext()
            ctx.Transactions.AddObject(_transaction)
            ctx.SaveChanges()
            Return _transaction
        End Using
    End Function

    Public Function UpdateTransaction(ByVal _transaction As Transaction) As Transaction
        Dim key As EntityKey = Nothing
        Dim original As Object = Nothing

        Using ctx As New IETrackerDataContext()
            key = ctx.CreateEntityKey(ENTITY_SET_NAME_TRANSACTIONS, _transaction)
            If ctx.TryGetObjectByKey(key, original) Then
                ctx.ApplyCurrentValues(key.EntitySetName, _transaction)
            End If
            ctx.SaveChanges()
        End Using

        Return _transaction
    End Function
#End Region

#Region "TransactionDescriptions"
    Public Function ReadAllTransactionDescriptions() As List(Of TransactionDescription)
        Dim _transactionDescriptions As List(Of TransactionDescription) = Nothing
        Using ctx As New IETrackerDataContext
            _transactionDescriptions = (From _trans In ctx.TransactionDescriptions).ToList
        End Using
        Return _transactionDescriptions
    End Function

    Public Function ReadTransactionDescription(_transactionDesc As String) As TransactionDescription
        Dim _transactionDescription As TransactionDescription = Nothing
        Using ctx As New IETrackerDataContext
            _transactionDescription = (From _trans In ctx.TransactionDescriptions Where _trans.TransactionDescription1 = _transactionDesc).FirstOrDefault
        End Using
        Return _transactionDescription
    End Function

    Public Function CreateTransctionDescription(_transactionDescription As TransactionDescription) As TransactionDescription
        Using ctx As New IETrackerDataContext()
            ctx.TransactionDescriptions.AddObject(_transactionDescription)
            ctx.SaveChanges()
        End Using
        Return _transactionDescription

    End Function
#End Region

#Region "TransactionTypes"
    Public Function ReadAllTransactionTypes() As List(Of TransactionType)
        Dim _transactionTransactionTypes As List(Of TransactionType) = Nothing
        Using ctx As New IETrackerDataContext
            _transactionTransactionTypes = (From _trans In ctx.TransactionTypes).ToList
        End Using
        Return _transactionTransactionTypes
    End Function

    Public Function ReadTransactionType(_transactionDescriptionID As Integer) As TransactionType
        Dim _transactionType As New TransactionType
        Using ctx As New IETrackerDataContext
            _transactionType = (From _trans In ctx.TransactionTypes Where _trans.TransactionDescID = _transactionDescriptionID).FirstOrDefault
        End Using
        Return _transactionType
    End Function

    Public Function CreateTransactionType(_transactionType As TransactionType) As TransactionType
        Using ctx As New IETrackerDataContext
            ctx.TransactionTypes.AddObject(_transactionType)
            ctx.SaveChanges()
        End Using
        Return _transactionType
    End Function
#End Region

#Region "TransactionTypeDescriptions"
    Public Function ReadAllTransactionTypeDescriptions() As List(Of TransactionTypeDescription)
        Dim _transactionTransactionTypeDescriptions As List(Of TransactionTypeDescription) = Nothing
        Using ctx As New IETrackerDataContext
            _transactionTransactionTypeDescriptions = (From _trans In ctx.TransactionTypeDescriptions).ToList
        End Using
        Return _transactionTransactionTypeDescriptions
    End Function
#End Region

#Region "Joined Query"
    Public Function GetAllTransactions(v_transactionType As String) As List(Of TransactionList)
        Dim transactionList As New List(Of TransactionList)
        Using ctx As New IETrackerDataContext
            Dim _transactions = From transDesc In ctx.TransactionDescriptions _
                                Join transType In ctx.TransactionTypes _
                                On transDesc.TransactionDescriptionID Equals transType.TransactionDescID _
                                Join trans In ctx.Transactions _
                                On transType.TransactionTypeID Equals trans.TransactionTypeID _
                                Join transTypeDesc In ctx.TransactionTypeDescriptions _
                                On transType.TransactionTypeDescID Equals transTypeDesc.TransactionTypeDescID _
                                Where transTypeDesc.TransactionTypeDesc.ToUpper.Contains(v_transactionType.ToUpper) _
                                Order By trans.TransactionDate Descending, transDesc.TransactionDescription1 Ascending
                                Select trans.TransactionID, trans.TransactionDate, trans.TransactionAmount, transDesc.TransactionDescription1, transTypeDesc.TransactionTypeDesc, trans.Comment

            If Not _transactions Is Nothing Then

                For Each tl In _transactions

                    transactionList.Add(New TransactionList( _
                                                    tl.TransactionID _
                                                          , tl.TransactionDate _
                                                          , tl.TransactionAmount _
                                                          , tl.TransactionDescription1 _
                                                          , tl.TransactionTypeDesc _
                                                         , tl.Comment _
                                                         ))

                Next
            End If
        End Using
        Return transactionList
    End Function
#End Region

#Region "Special Queries"
    Public Function GetAllTransactionDescription(v_transactionType As String) As List(Of String)
        Dim transactionDescriptionList As New List(Of String)
        Using ctx As New IETrackerDataContext
            transactionDescriptionList = (From transDesc In ctx.TransactionDescriptions _
                        Join transType In ctx.TransactionTypes _
                        On transDesc.TransactionDescriptionID Equals transType.TransactionDescID _
                        Join transTypeDesc In ctx.TransactionTypeDescriptions _
                        On transType.TransactionTypeDescID Equals transTypeDesc.TransactionTypeDescID _
                        Where transTypeDesc.TransactionTypeDesc.ToUpper.Contains(v_transactionType.ToUpper) _
                        Order By transDesc.TransactionDescription1 _
                        Select transDesc.TransactionDescription1).ToList
        End Using
        Return transactionDescriptionList
    End Function
#End Region
End Class
